using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Npgsql;

namespace AuthService.Infrastructure.Database;

/// <summary>
/// 数据库初始化器
/// 负责在应用启动时自动创建数据库、检查连接、验证权限
/// 支持PostgreSQL数据库的自动化部署和初始化
/// </summary>
public class DatabaseInitializer
{
    private readonly IConfiguration _configuration;
    private readonly ILogger<DatabaseInitializer> _logger;

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="configuration">应用配置，用于获取数据库连接字符串</param>
    /// <param name="logger">日志记录器</param>
    public DatabaseInitializer(IConfiguration configuration, ILogger<DatabaseInitializer> logger)
    {
        _configuration = configuration;
        _logger = logger;
    }

    /// <summary>
    /// 数据库初始化的主要方法
    /// 执行完整的数据库初始化流程：连接检查 → 权限验证 → 数据库创建 → 连接验证
    /// </summary>
    /// <returns>初始化结果，包含成功状态、错误信息和建议解决方案</returns>
    public async Task<DatabaseInitResult> InitializeAsync()
    {
        var result = new DatabaseInitResult();

        try
        {
            var connectionString = _configuration.GetConnectionString("DefaultConnection");
            if (string.IsNullOrEmpty(connectionString))
            {
                result.Success = false;
                result.ErrorMessage = "数据库连接字符串未配置";
                return result;
            }

            var builder = new NpgsqlConnectionStringBuilder(connectionString);
            var databaseName = builder.Database;

            if (string.IsNullOrEmpty(databaseName))
            {
                result.Success = false;
                result.ErrorMessage = "连接字符串中未指定数据库名称";
                return result;
            }

            result.DatabaseName = databaseName;
            result.Host = builder.Host;
            result.Port = builder.Port;
            result.Username = builder.Username;

            _logger.LogInformation("开始数据库初始化检查...");
            _logger.LogInformation("目标数据库: {DatabaseName}", databaseName);
            _logger.LogInformation("主机: {Host}:{Port}", builder.Host, builder.Port);
            _logger.LogInformation("用户: {Username}", builder.Username);

            // === 第1步：快速检查目标数据库是否已存在且可连接 ===
            // 如果目标数据库已经存在且可以连接，则跳过后续的创建步骤
            // 这是一个优化措施，避免不必要的权限检查和创建操作
            var canConnectToTarget = await CanConnectToDatabaseAsync(connectionString, databaseName);
            if (canConnectToTarget)
            {
                _logger.LogInformation("✅ 可以直接连接到目标数据库 {DatabaseName}", databaseName);
                result.Success = true;
                result.DatabaseExists = true;
                result.CanConnect = true;
                return result; // 直接返回成功结果
            }

            // === 第2步：检查PostgreSQL服务器连接 ===
            // 如果无法连接到目标数据库，尝试连接到postgres系统数据库
            // 这用于验证PostgreSQL服务器是否正常运行以及连接参数是否正确
            builder.Database = "postgres";
            var postgresConnectionString = builder.ToString();

            var canConnectToPostgres = await CanConnectToDatabaseAsync(postgresConnectionString, "postgres");
            if (!canConnectToPostgres)
            {
                result.Success = false;
                result.ErrorMessage = "无法连接到 PostgreSQL 服务器，请检查连接配置";
                return result;
            }

            _logger.LogInformation("✅ 可以连接到 postgres 数据库");

            // === 第3步：检查用户创建数据库权限 ===
            // 验证当前用户是否具有CREATEDB权限
            // 这是创建新数据库的前提条件
            var hasCreateDbPermission = await CheckCreateDatabasePermissionAsync(postgresConnectionString, builder.Username);
            if (!hasCreateDbPermission)
            {
                result.Success = false;
                result.ErrorMessage = $"用户 '{builder.Username}' 没有创建数据库的权限。请执行: ALTER USER {builder.Username} CREATEDB;";
                result.SuggestedSolution = GetPermissionSolution(builder.Username);
                return result;
            }

            _logger.LogInformation("✅ 用户具有创建数据库权限");

            // === 第4步：创建目标数据库 ===
            // 使用CREATE DATABASE命令创建新的数据库
            var createResult = await CreateDatabaseAsync(postgresConnectionString, databaseName);
            if (!createResult)
            {
                result.Success = false;
                result.ErrorMessage = "创建数据库失败";
                return result;
            }

            // === 第5步：验证数据库创建成功 ===
            // 尝试连接到新创建的数据库，确保创建操作成功
            var verifyResult = await CanConnectToDatabaseAsync(connectionString, databaseName);
            if (!verifyResult)
            {
                result.Success = false;
                result.ErrorMessage = "数据库创建成功但无法连接";
                return result;
            }

            _logger.LogInformation("✅ 数据库初始化完成");
            result.Success = true;
            result.DatabaseExists = true;
            result.CanConnect = true;
            result.DatabaseCreated = true;

            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "数据库初始化过程中发生错误");
            result.Success = false;
            result.ErrorMessage = ex.Message;
            return result;
        }
    }

    /// <summary>
    /// 检查是否可以连接到指定数据库
    /// </summary>
    private async Task<bool> CanConnectToDatabaseAsync(string connectionString, string databaseName)
    {
        try
        {
            using var connection = new NpgsqlConnection(connectionString);
            await connection.OpenAsync();
            _logger.LogDebug("成功连接到数据库: {DatabaseName}", databaseName);
            return true;
        }
        catch (PostgresException ex) when (ex.SqlState == "3D000")
        {
            _logger.LogDebug("数据库 {DatabaseName} 不存在", databaseName);
            return false;
        }
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "连接数据库 {DatabaseName} 失败", databaseName);
            return false;
        }
    }

    /// <summary>
    /// 检查用户是否有创建数据库的权限
    /// </summary>
    private async Task<bool> CheckCreateDatabasePermissionAsync(string connectionString, string username)
    {
        try
        {
            using var connection = new NpgsqlConnection(connectionString);
            await connection.OpenAsync();

            var command = new NpgsqlCommand(
                "SELECT usecreatedb FROM pg_user WHERE usename = @username",
                connection);
            command.Parameters.AddWithValue("username", username);

            var result = await command.ExecuteScalarAsync();
            return result is bool canCreateDb && canCreateDb;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "检查用户权限时发生错误");
            return false;
        }
    }

    /// <summary>
    /// 创建数据库
    /// </summary>
    private async Task<bool> CreateDatabaseAsync(string connectionString, string databaseName)
    {
        try
        {
            using var connection = new NpgsqlConnection(connectionString);
            await connection.OpenAsync();

            // 检查数据库是否已存在
            var checkCommand = new NpgsqlCommand(
                "SELECT 1 FROM pg_database WHERE datname = @databaseName",
                connection);
            checkCommand.Parameters.AddWithValue("databaseName", databaseName);

            var exists = await checkCommand.ExecuteScalarAsync();
            if (exists != null)
            {
                _logger.LogInformation("数据库 {DatabaseName} 已存在", databaseName);
                return true;
            }

            // 创建数据库
            _logger.LogInformation("正在创建数据库: {DatabaseName}", databaseName);
            var createCommand = new NpgsqlCommand(
                $"CREATE DATABASE \"{databaseName}\" WITH ENCODING='UTF8'",
                connection);

            await createCommand.ExecuteNonQueryAsync();
            _logger.LogInformation("数据库 {DatabaseName} 创建成功", databaseName);

            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "创建数据库 {DatabaseName} 失败", databaseName);
            return false;
        }
    }

    /// <summary>
    /// 获取权限问题的解决方案
    /// </summary>
    private string GetPermissionSolution(string username)
    {
        return $@"
请以超级用户身份连接到 PostgreSQL 并执行以下命令：

1. 连接到 postgres 数据库：
   psql -h localhost -U postgres -d postgres

2. 授予创建数据库权限：
   ALTER USER {username} CREATEDB;

3. 验证权限：
   SELECT usename, usecreatedb FROM pg_user WHERE usename = '{username}';

4. 退出：
   \q

或者，您可以手动创建数据库：
   CREATE DATABASE authservice OWNER {username};
";
    }
}

/// <summary>
/// 数据库初始化结果
/// </summary>
public class DatabaseInitResult
{
    public bool Success { get; set; }
    public string? ErrorMessage { get; set; }
    public string? SuggestedSolution { get; set; }
    public string? DatabaseName { get; set; }
    public string? Host { get; set; }
    public int Port { get; set; }
    public string? Username { get; set; }
    public bool DatabaseExists { get; set; }
    public bool CanConnect { get; set; }
    public bool DatabaseCreated { get; set; }
}
